﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Configuration;

namespace UserDemo
{
    /// <summary>
    /// 数据库 操作类
    /// </summary>
    public class DbHelper
    {
        private SqlConnection GetConnect()
        {
            //从配置文件中 读取数据库连接
            var connectionString = WebConfigurationManager.ConnectionStrings["default"].ConnectionString; //Web
            return new SqlConnection(connectionString);
        }

        /// <summary>
        /// 按行 读取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string sql, params SqlParameter[] parameters)
        {
            var conn = GetConnect();
            var cmd = new SqlCommand(sql, conn);

            cmd.Parameters.AddRange(parameters);
            cmd.Connection.Open();
            var dataReader = cmd.ExecuteReader();
            return dataReader;

        }


        /// <summary>
        /// 执行Sql 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns>受影响的行数</returns>
        public int Execute(string sql, params SqlParameter[] parameters)
        {
            var conn = GetConnect();
            var cmd = new SqlCommand(sql, conn);
            try
            {
                cmd.Parameters.AddRange(parameters);
                cmd.Connection.Open();
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                cmd.Dispose();
            }
        }

        /// <summary>
        /// 执行sql 返回首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            var conn = GetConnect();
            var cmd = new SqlCommand(sql, conn);
            try
            {
                cmd.Parameters.AddRange(parameters);
                cmd.Connection.Open();
                return cmd.ExecuteScalar();
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                cmd.Dispose();
            }
        }



    }
}